Skip Headers

Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 5 Rules

This chapter explains the concepts related to rules.

This chapter contains these topics:

- The Components of a Rule

- Rule Set Evaluation

- Database Objects and Privileges Related to Rules

  See Also: Chapter 6, "How Rules Are Used in Streams" Chapter 14, "Managing Rules" Chapter 28, "Rule-Based Application Example"

## The Components of a Rule

A rule is a database object that enables a client to perform an action when an event occurs and a condition is satisfied. A rule consists of the following components:

- Rule Condition

- Rule Evaluation Context (optional)

- Rule Action Context (optional)

Each rule is specified as a condition that is similar to the condition in the `WHERE` clause of a SQL query. You can group related rules together into rule sets. A single rule can be in one rule set, multiple rule sets, or no rule sets.

Rule sets are evaluated by a rules engine, which is a built-in part of Oracle. Both user-created applications and Oracle features, such as Streams, can be clients of the rules engine.

Note: A rule must be in a rule set for it to be evaluated.

### Rule Condition

A rule condition combines one or more expressions and conditions and returns a Boolean value, which is a value of `TRUE`, `FALSE`, or `NULL` (unknown). An expression is a combination of one or more values and operators that evaluate to a value. A value can be data in a table, data in variables, or data returned by a SQL function or a PL/SQL function. For example, the following expression includes only a single value:

    salary

The following expression includes two values (`salary` and `.1`) and an operator (`*`):

    salary * .1

The following condition consists of two expressions (`salary` and `3800`) and a condition (`=`):

    salary = 3800

This logical condition evaluates to `TRUE` for a given row when the `salary` column is `3800`. Here, the value is data in the `salary` column of a table.

A single rule condition can include more than one condition combined with the `AND`, `OR`, and `NOT` logical conditions to a form compound condition. A logical condition combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. For example, consider the following compound condition:

    salary = 3800 OR job_title = 'Programmer'

This rule condition contains two conditions joined by the `OR` logical condition. If either condition evaluates to `TRUE`, then the rule condition evaluates to `TRUE`. If the logical condition were `AND` instead of `OR`, then both conditions must evaluate to `TRUE` for the entire rule condition to evaluate to `TRUE`.

#### Variables in Rule Conditions

Rule conditions can contain variables. When you use variables in rule conditions, precede each variable with a colon (:). The following is an example of a variable used in a rule condition:

    :x = 55

Variables let you refer to data that is not stored in a table. A variable can also improve performance by replacing a commonly occurring expression. Performance can improve because, instead of evaluating the same expression multiple times, the variable is evaluated once.

A rule condition can also contain an evaluation of a call to a subprogram. Such a condition is evaluated in the same way as other conditions. That is, it evaluates to a value of `TRUE`, `FALSE`, or `NULL` (unknown). The following is an example of a condition that contains a call to a simple function named `is_manager` that determines whether an employee is a manager:

    is_manager(employee_id) = 'Y'

Here, the value of `employee_id` is determined by data in a table where `employee_id` is a column.

You can use user-defined types for variables. Therefore, variables can have attributes. When a variable has attributes, each attribute contains partial data for the variable. In rule conditions, you specify attributes using dot notation. For example, the following condition evaluates to `TRUE` if the value of attribute `z` in variable `y` is `9`:

    :y.z = 9

Note: A rule cannot have a `NULL` (or empty) rule condition.

See Also: Oracle Database SQL Reference for more information about conditions, expressions, and operators Oracle Database Application Developer's Guide - Object-Relational Features for more information about user-defined types

#### Simple Rule Conditions

A simple rule condition is a condition that has one of the following forms:

- `simple_rule_expression condition constant`

- `constant condition simple_rule_expression`

- `constant condition constant`

##### Simple Rule Expressions

In a simple rule condition, a `simple_rule_expression` is one of the following:

- Table column.

- Variable.

- Variable attribute.

- Method result where the method either takes no arguments or constant arguments and the method result can be returned by the variable method function, so that the expression is one of the datatypes supported for simple rules. Such methods include LCR member subprograms that meet these requirements, such as `GET_TAG`, `GET_VALUE`, `GET_COMPATIBLE`, `GET_EXTRA_ATTRIBUTE`, and so on.

For table columns, variables, variable attributes, and method results, the following datatypes can be used in simple rule conditions:

- `VARCHAR2`

- `NVARCHAR2`

- `NUMBER`

- `DATE`

- `BINARY_FLOAT`

- `BINARY_DOUBLE`

- `TIMESTAMP`

- `TIMESTAMP` `WITH` `TIME` `ZONE`

- `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE`

- `RAW`

- `CHAR`

Use of other datatypes in expressions results in nonsimple rule conditions.

##### Conditions

In a simple rule condition, a `condition` is one of the following:

- `<=`

- `<`

- `=`

- `>`

- `>=`

- `!=`

- `IS NULL`

- `IS NOT NULL`

Use of other conditions results in nonsimple rule conditions.

##### Constants

A `constant` is a fixed value. A constant can be:

- A number, such as `12` or `5.4`

- A character, such as `x` or `$`

- A character string, such as `"this` `is` `a` `string"`

##### Examples of Simple Rule Conditions

The following conditions are simple rule conditions, assuming the datatypes used in expressions are supported in simple rule conditions:

- `tab1.col = 5`

- `tab2.col != 5`

- `:v1 > 'aaa'`

- `:v2.a1 < 10.01`

- `:v3.m() = 10`

- `:v4 IS NOT NULL`

- `1 = 1`

- `'abc' > 'AB'`

- `:date_var < to_date('04-01-2004, 14:20:17', 'mm-dd-yyyy, hh24:mi:ss')`

- `:adt_var.ts_attribute >= to_timestamp('04-01-2004, 14:20:17 PST', 'mm-dd-yyyy, hh24:mi:ss TZR')`

- `:my_var.my_to_upper('abc') = 'ABC'`

Rules with simple rule conditions are called simple rules. You can combine two or more simple conditions with the logical conditions `AND` and `OR` for a rule, and the rule remains simple. For example, rules with the following conditions are simple rules:

- `tab1.col = 5 AND :v1 > 'aaa'`

- `tab1.col = 5 OR :v1 > 'aaa'`

However, using the `NOT` logical condition in a rule condition causes the rule to be nonsimple.

##### Benefits of Simple Rules

Simple rules are important for the following reasons:

- Simple rules are indexed by the rules engine internally.

- Simple rules can be evaluated without executing SQL.

- Simple rules can be evaluated with partial data.

When a client uses the `DBMS_RULE.EVALUATE` procedure to evaluate an event, the client can specify that only simple rules should be evaluated by specifying `true` for the `simple_rules_only` parameter.

See Also: Oracle Database SQL Reference for more information about conditions and logical conditions Oracle Database PL/SQL Packages and Types Reference for more information about LCR types and their member subprograms

### Rule Evaluation Context

An evaluation context is a database object that defines external data that can be referenced in rule conditions. The external data can exist as variables, table data, or both. The following analogy might be helpful: If the rule condition were the `WHERE` clause in a SQL query, then the external data in the evaluation context would be the tables and bind variables referenced in the `FROM` clause of the query. That is, the expressions in the rule condition should reference the tables, table aliases, and variables in the evaluation context to make a valid `WHERE` clause.

A rule evaluation context provides the necessary information for interpreting and evaluating the rule conditions that reference external data. For example, if a rule refers to a variable, then the information in the rule evaluation context must contain the variable type. Or, if a rule refers to a table alias, then the information in the evaluation context must define the table alias.

The objects referenced by a rule are determined by the rule evaluation context associated with it. The rule owner must have the necessary privileges to access these objects, such as `SELECT` privilege on tables, `EXECUTE` privilege on types, and so on. The rule condition is resolved in the schema that owns the evaluation context.

For example, consider a rule evaluation context named `hr_evaluation_context` that contains the following information:

- Table alias `dep` corresponds to the `hr.departments` table.

- Variables `loc_id1` and `loc_id2` are both of type `NUMBER`.

The `hr_evaluation_context` rule evaluation context provides the necessary information for evaluating the following rule condition:

    dep.location_id IN (:loc_id1, :loc_id2)

In this case, the rule condition evaluates to `TRUE` for a row in the `hr.departments` table if that row has a value in the `location_id` column that corresponds to either of the values passed in by the `loc_id1` or `loc_id2` variables. The rule cannot be interpreted or evaluated properly without the information in the `hr_evaluation_context` rule evaluation context. Also, notice that dot notation is used to specify the column `location_id` in the `dep` table alias.

Note: Views are not supported as base tables in evaluation contexts.

#### Explicit and Implicit Variables

The value of a variable referenced in a rule condition can be explicitly specified when the rule is evaluated, or the value of a variable can be implicitly available given the event.

Explicit variables are supplied by the caller at evaluation time. These values are specified by the `variable_values` parameter when the `DBMS_RULE.EVALUATE` procedure is run.

Implicit variables are not given a value supplied by the caller at evaluation time. The value of an implicit variable is obtained by calling the variable value function. You define this function when you specify the `variable_types` list during the creation of an evaluation context using the `CREATE_EVALUATION_CONTEXT` procedure in the `DBMS_RULE_ADM` package. If the value for an implicit variable is specified during evaluation, then the specified value overrides the value returned by the variable value function.

Specifically, the `variable_types` list is of type `SYS.RE$VARIABLE_TYPE_LIST`, which is a list of variables of type `SYS.RE$VARIABLE_TYPE`. Within each instance of `SYS.RE$VARIABLE_TYPE` in the list, the function used to determine the value of an implicit variable is specified as the `variable_value_function` attribute.

Whether variables are explicit or implicit is the choice of the designer of the application using the rules engine. The following are reasons for using an implicit variable:

- The caller of the `DBMS_RULE.EVALUATE` procedure does not need to know anything about the variable, which can reduce the complexity of the application using the rules engine. For example, a variable can call a function that returns a value based on the data being evaluated.

- The caller might not have `EXECUTE` privileges on the variable value function.

- The caller of the `DBMS_RULE.EVALUATE` procedure does not know the variable value based on the event, which can improve security if the variable value contains confidential information.

- The variable will be used infrequently, and the variable's value always can be derived if necessary. Making such variables implicit means that the caller of the `DBMS_RULE.EVALUATE` procedure does not need to specify many uncommon variables.

For example, in the following rule condition, the values of variable `x` and variable `y` could be specified explicitly, but the value of the variable `max` could be returned by running the `max` function:

    :x = 4 AND :y < :max

Alternatively, variable `x` and `y` could be implicit variables, and variable `max` could be an explicit variable. So, there is no syntactic difference between explicit and implicit variables in the rule condition. You can determine whether a variable is explicit or implicit by querying the `DBA_EVALUATION_CONTEXT_VARS` data dictionary view. For explicit variables, the `VARIABLE_VALUE_FUNCTION` field is `NULL`. For implicit variables, this field contains the name of the function called by the implicit variable.

See Also: Oracle Database PL/SQL Packages and Types Reference for more information about the `DBMS_RULE` and `DBMS_RULE_ADM` packages, and for more information about the Oracle-supplied rule types Oracle Database Reference for more information about the `DBA_EVALUATION_CONTEXT_VARS` data dictionary view

#### Evaluation Context Association with Rule Sets and Rules

To be evaluated, each rule must be associated with an evaluation context or must be part of a rule set that is associated with an evaluation context. A single evaluation context can be associated with multiple rules or rule sets. The following list describes which evaluation context is used when a rule is evaluated:

- If an evaluation context is associated with a rule, then it is used for the rule whenever the rule is evaluated, and any evaluation context associated with the rule set being evaluated is ignored.

- If a rule does not have an evaluation context, but an evaluation context was specified for the rule when it was added to a rule set using the `ADD_RULE` procedure in the `DBMS_RULE_ADM` package, then the evaluation context specified in the `ADD_RULE` procedure is used for the rule when the rule set is evaluated.

- If no rule evaluation context is associated with a rule and none was specified by the `ADD_RULE` procedure, then the evaluation context of the rule set is used for the rule when the rule set is evaluated.

  Note: If a rule does not have an evaluation context, and you try to add it to a rule set that does not have an evaluation context, then an error is raised, unless you specify an evaluation context when you run the `ADD_RULE` procedure.

#### Evaluation Function

You have the option of creating an evaluation function to be run with a rule evaluation context. You can use an evaluation function for the following reasons:

- You want to bypass the rules engine and instead evaluate events using the evaluation function.

- You want to filter events so that some events are evaluated by the evaluation function and other events are evaluated by the rules engine.

You associate a function with a rule evaluation context by specifying the function name for the `evaluation_function` parameter when you create the rule evaluation context with the `CREATE_EVALUATION_CONTEXT` procedure in the `DBMS_RULE_ADM` package. The rules engine invokes the evaluation function during the evaluation of any rule set that uses the evaluation context.

The `DBMS_RULE.EVALUATE` procedure is overloaded. The function must have each parameter in one of the `DBMS_RULE.EVALUATE` procedures, and the type of each parameter must be same as the type of the corresponding parameter in the `DBMS_RULE.EVALUATE` procedure, but the names of the parameters can be different.

An evaluation function has the following return values:

- `DBMS_RULE_ADM.EVALUATION_SUCCESS`: The user specified evaluation function completed the rule set evaluation successfully. The rules engine returns the results of the evaluation obtained by the evaluation function to the rules engine client using the `DBMS_RULE.EVALUATE` procedure.

- `DBMS_RULE_ADM.EVALUATION_CONTINUE`: The rules engine evaluates the rule set as if there were no evaluation function. The evaluation function is not used, and any results returned by the evaluation function are ignored.

- `DBMS_RULE_ADM.EVALUATION_FAILURE`: The user-specified evaluation function failed. Rule set evaluation stops, and an error is raised.

If you always want to bypass the rules engine, then the evaluation function should return either `EVALUATION_SUCCESS` or `EVALUATION_FAILURE`. However, if you want to filter events so that some events are evaluated by the evaluation function and other events are evaluated by the rules engine, then the evaluation function can return all three return values, and it returns `EVALUATION_CONTINUE` when the rules engine should be used for evaluation.

If you specify an evaluation function for an evaluation context, then the evaluation function is run during evaluation when the evaluation context is used by a rule set or rule.

See Also: Oracle Database PL/SQL Packages and Types Reference for more information about the evaluation function specified in the `DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT` procedure and for more information about the overloaded `DBMS_RULE.EVALUATE` procedure

### Rule Action Context

An action context contains optional information associated with a rule that is interpreted by the client of the rules engine when the rule is evaluated for an event. The client of the rules engine can be a user-created application or an internal feature of Oracle, such as Streams. Each rule has only one action context. The information in an action context is of type `SYS.RE$NV_LIST`, which is a type that contains an array of name-value pairs.

The rule action context information provides a context for the action taken by a client of the rules engine when a rule evaluates to `TRUE` or `MAYBE`. The rules engine does not interpret the action context. Instead, it returns the action context, and a client of the rules engine can interpret the action context information.

For example, suppose an event is defined as the addition of a new employee to a company. If the employee information is stored in the `hr.employees` table, then the event occurs whenever a row is inserted into this table. The company wants to specify that a number of actions are taken when a new employee is added, but the actions depend on which department the employee joins. One of these actions is that the employee is registered for a course relating to the department.

In this scenario, the company can create a rule for each department with an appropriate action context. Here, an action context returned when a rule evaluates to `TRUE` specifies the number of a course that an employee should take. Here are parts of the rule conditions and the action contexts for three departments:

Rule Name | Part of the Rule Condition | Action Context Name-Value Pair
--- | --- | ---
`rule_dep_10` | `department_id = 10` | `course_number`, `1057`
`rule_dep_20` | `department_id = 20` | `course_number`, `1215`
`rule_dep_30` | `department_id = 30` | `NULL`

These action contexts return the following instructions to the client application:

- The action context for the `rule_dep_10` rule instructs the client application to enroll the new employee in course number `1057`.

- The action context for the `rule_dep_20` rule instructs the client application to enroll the new employee in course number `1215`.

- The `NULL` action context for the `rule_dep_30` rule instructs the client application not to enroll the new employee in any course.

Each action context can contain zero or more name-value pairs. If an action context contains more than one name-value pair, then each name in the list must be unique. In this example, the client application to which the rules engine returns the action context registers the new employee in the course with the returned course number. The client application does not register the employee for a course if a `NULL` action context is returned or if the action context does not contain a course number.

If multiple clients use the same rule, or if you want an action context to return more than one name-value pair, then you can list more than one name-value pair in an action context. For example, suppose the company also adds a new employee to a department electronic mailing list. In this case, the action context for the `rule_dep_10` rule might contain two name-value pairs:

Name | Value
--- | ---
`course_number` | `1057`
`dist_list` | `admin_list`

The following are considerations for names in name-value pairs:

- If different applications use the same action context, then use different names or prefixes of names to avoid naming conflicts.

- Do not use $ and # in names because they can cause conflicts with Oracle-supplied action context names.

You add a name-value pair to an action context using the `ADD_PAIR` member procedure of the `RE$NV_LIST` type. You remove a name-value pair from an action context using the `REMOVE_PAIR` member procedure of the `RE$NV_LIST` type. If you want to modify an existing name-value pair in an action context, then you should first remove it using the `REMOVE_PAIR` member procedure and then add an appropriate name-value pair using the `ADD_PAIR` member procedure.

An action context cannot contain information of the following datatypes:

- `CLOB`

- `NCLOB`

- `BLOB`

- `LONG`

- `LONG` `RAW`

In addition, an action context cannot contain object types with attributes of these datatypes, or object types that use type evolution or type inheritance.

Note: Streams uses action contexts for custom rule-based transformations and, when subset rules are specified, for internal transformations that might be required on LCRs containing `UPDATE` operations. Streams also uses action contexts to specify a destination queue into which an apply process enqueues messages that satisfy the rule. In addition, Streams uses action contexts to specify whether or not a message that satisfies an apply process rule is executed by the apply process.

See Also: "Streams and Action Contexts" "Creating a Rule with an Action Context" and "Altering a Rule" for examples that add and modify name-value pairs Oracle Database PL/SQL Packages and Types Reference for more information about the `RE$NV_LIST` type

## Rule Set Evaluation

The rules engine evaluates rule sets against an event. An event is an occurrence that is defined by the client of the rules engine. The client initiates evaluation of an event by calling the `DBMS_RULE.EVALUATE` procedure. This procedure enables the client to send some information about the event to the rules engine for evaluation against a rule set. The event itself can have more information than the information that the client sends to the rules engine.

The following information is specified by the client when it calls the `DBMS_RULE.EVALUATE` procedure:

- The name of the rule set that contains the rules to use to evaluate the event.

- The evaluation context to use for evaluation. Only rules that use the specified evaluation context are evaluated.

- Table values and variable values. The table values contain rowids that refer to the data in table rows, and the variable values contain the data for explicit variables. Values specified for implicit variables override the values that might be obtained using a variable value function. If a specified variable has attributes, then the client can send a value for the entire variable, or the client can send values for any number of the attributes of the variable. However, clients cannot specify attribute values if the value of the entire variable is specified.

- An optional event context. An event context is a varray of type `SYS.RE$NV_LIST` that contains name-value pairs that contain information about the event. This optional information is not used directly or interpreted by the rules engine. Instead, it is passed to client callbacks, such as an evaluation function, a variable value function (for implicit variables), and a variable method function.

The client can also send other information about how to evaluate an event against the rule set using the `DBMS_RULE.EVALUATE` procedure. For example, the caller can specify if evaluation must stop as soon as the first `TRUE` rule or the first `MAYBE` rule (if there are no `TRUE` rules) is found.

If the client wants all of the rules that evaluate to `TRUE` or `MAYBE` returned to it, then the client can specify whether evaluation results should be sent back in a complete list of the rules that evaluated to `TRUE` or `MAYBE`, or evaluation results should be sent back iteratively. When evaluation results are sent iteratively to the client, the client can retrieve each rule that evaluated to `TRUE` or `MAYBE` one by one using the `GET_NEXT_HIT` function in the `DBMS_RULE` package.

The rules engine uses the rules in the specified rule set for evaluation and returns the results to the client. The rules engine returns rules using two `OUT` parameters in the `EVALUATE` procedure. This procedure is overloaded and the two `OUT` parameters are different in each version of the procedure:

- One version of the procedure returns all of the rules that evaluate to `TRUE` in one list or all of the rules that evaluate to `MAYBE` in one list, and the two `OUT` parameters for this version of the procedure are `true_rules` and `maybe_rules`. That is, the `true_rules` parameter returns rules in one list that evaluate to `TRUE`, and the `maybe_rules` parameter returns rules in one list that might evaluate to `TRUE` given more information.

- The other version of the procedure returns all of the rules that evaluate to `TRUE` or `MAYBE` iteratively at the request of the client, and the two `OUT` parameters for this version of the procedure are `true_rules_iterator` and `maybe_rules_iterator`. That is, the `true_rules_iterator` parameter returns rules that evaluate to `TRUE` one by one, and the `maybe_rules_iterator` parameter returns rules one by one that might evaluate to `TRUE` given more information.

### Rule Set Evaluation Process

Figure 5-1 shows the rule set evaluation process:

1. A client-defined event occurs.

2. The client initiates evaluation of a rule set by sending information about an event to the rules engine using the `DBMS_RULE.EVALUATE` procedure.

3. The rules engine evaluates the rule set for the event using the relevant evaluation context. The client specifies both the rule set and the evaluation context in the call to the `DBMS_RULE.EVALUATE` procedure. Only rules that are in the specified rule set, and use the specified evaluation context, are used for evaluation.

4. The rules engine obtains the results of the evaluation. Each rule evaluates to either `TRUE`, `FALSE`, or `NULL` (unknown).

5. The rules engine returns rules that evaluated to `TRUE` to the client, either in a complete list or one by one. Each returned rule is returned with its entire action context, which can contain information or can be `NULL`.

6. The client performs actions based on the results returned by the rules engine. The rules engine does not perform actions based on rule evaluations.

Figure 5-1 Rule Set Evaluation

Description of "Figure 5-1 Rule Set Evaluation"

See Also: Oracle Database PL/SQL Packages and Types Reference for more information about the `DBMS_RULE.EVALUATE` procedure "Rule Conditions with Undefined Variables that Evaluate to NULL" for information about Streams clients and `maybe_rules`

### Partial Evaluation

Partial evaluation occurs when the `DBMS_RULE.EVALUATE` procedure is run without data for all the tables and variables in the specified evaluation context. During partial evaluation, some rules can reference columns, variables, or attributes that are unavailable, while some other rules can reference only available data.

For example, consider a scenario where only the following data is available during evaluation:

- Column `tab1.col` `=` `7`

- Attribute `v1.a1` `=` `'ABC'`

The following rules are used for evaluation:

- Rule `R1` has the following condition:

      (tab1.col = 5)

- Rule `R2` has the following condition:

      (:v1.a2 > 'aaa')

- Rule `R3` has the following condition:

      (:v1.a1 = 'ABC') OR (:v2 = 5)

- Rule `R4` has the following condition:

      (:v1.a1 = UPPER('abc'))

Given this scenario, `R1` and `R4` reference available data, `R2` references unavailable data, and `R3` references available data and unavailable data.

Partial evaluation always evaluates only simple conditions within a rule. If the rule condition has parts which are not simple, then the rule might or might not be evaluated completely, depending on the extent to which data is available. If a rule is not completely evaluated, then it can be returned as a `MAYBE` rule.

Given the rules in this scenario, `R1` and the first part of `R3` are evaluated, but `R2` and `R4` are not evaluated. The following results are returned to the client:

- `R1` evaluates to `FALSE`, and so is not returned.

- `R2` is returned as `MAYBE` because information about attribute `v1.a2` is not available.

- `R3` is returned as `TRUE` because `R3` is a simple rule and the value of `v1.a1` matches the first part of the rule condition.

- `R4` is returned as `MAYBE` because the rule condition is not simple. The client must supply the value of variable `v1` for this rule to evaluate to `TRUE` or `FALSE`.

  See Also: "Simple Rule Conditions"

## Database Objects and Privileges Related to Rules

You can create the following types of database objects directly using the `DBMS_RULE_ADM` package:

- Evaluation contexts

- Rules

- Rule sets

You can create rules and rule sets indirectly using the `DBMS_STREAMS_ADM` package. You control the privileges for these database objects using the following procedures in the `DBMS_RULE_ADM` package:

- `GRANT_OBJECT_PRIVILEGE`

- `GRANT_SYSTEM_PRIVILEGE`

- `REVOKE_OBJECT_PRIVILEGE`

- `REVOKE_SYSTEM_PRIVILEGE`

To allow a user to create rule sets, rules, and evaluation contexts in the user's own schema, grant the user the following system privileges:

- `CREATE_RULE_SET_OBJ`

- `CREATE_RULE_OBJ`

- `CREATE_EVALUATION_CONTEXT_OBJ`

These privileges, and the privileges discussed in the following sections, can be granted to the user directly or through a role.

Note: When you grant a privilege on `"ANY"` object (for example, `ALTER_ANY_RULE`), and the initialization parameter `O7_DICTIONARY_ACCESSIBILITY` is set to `false`, you give the user access to that type of object in all schemas except the `SYS` schema. By default, the initialization parameter `O7_DICTIONARY_ACCESSIBILITY` is set to `false`. If you want to grant access to an object in the `SYS` schema, then you can grant object privileges explicitly on the object. Alternatively, you can set the `O7_DICTIONARY_ACCESSIBILITY` initialization parameter to `true`. Then privileges granted on `"ANY"` object will allow access to any schema, including `SYS`.

See Also: "The Components of a Rule" for more information about these database objects Oracle Database PL/SQL Packages and Types Reference for more information about the system and object privileges for these database objects Oracle Database Concepts and Oracle Database Security Guide for general information about user privileges Chapter 6, "How Rules Are Used in Streams" for more information about creating rules and rule sets indirectly using the `DBMS_STREAMS_ADM` package

### Privileges for Creating Database Objects Related to Rules

To create an evaluation context, rule, or rule set in a schema, a user must meet at least one of the following conditions:

- The schema must be the user's own schema, and the user must be granted the create system privilege for the type of database object being created. For example, to create a rule set in the user's own schema, a user must be granted the `CREATE_RULE_SET_OBJ` system privilege.

- The user must be granted the create any system privilege for the type of database object being created. For example, to create an evaluation context in any schema, a user must be granted the `CREATE_ANY_EVALUATION_CONTEXT` system privilege.

  Note: When creating a rule with an evaluation context, the rule owner must have privileges on all objects accessed by the evaluation context.

### Privileges for Altering Database Objects Related to Rules

To alter an evaluation context, rule, or rule set, a user must meet at least one of the following conditions:

- The user must own the database object.

- The user must be granted the alter object privilege for the database object if it is in another user's schema. For example, to alter a rule set in another user's schema, a user must be granted the `ALTER_ON_RULE_SET` object privilege on the rule set.

- The user must be granted the alter any system privilege for the database object. For example, to alter a rule in any schema, a user must be granted the `ALTER_ANY_RULE` system privilege.

### Privileges for Dropping Database Objects Related to Rules

To drop an evaluation context, rule, or rule set, a user must meet at least one of the following conditions:

- The user must own the database object.

- The user must be granted the drop any system privilege for the database object. For example, to drop a rule set in any schema, a user must be granted the `DROP_ANY_RULE_SET` system privilege.

### Privileges for Placing Rules in a Rule Set

This section describes the privileges required to place a rule in a rule set. The user must meet at least one of the following conditions for the rule:

- The user must own the rule.

- The user must be granted the execute object privilege on the rule if the rule is in another user's schema. For example, to place a rule named `depts` in the `hr` schema in a rule set, a user must be granted the `EXECUTE_ON_RULE` privilege for the `hr.depts` rule.

- The user must be granted the execute any system privilege for rules. For example, to place any rule in a rule set, a user must be granted the `EXECUTE_ANY_RULE` system privilege.

The user also must meet at least one of the following conditions for the rule set:

- The user must own the rule set.

- The user must be granted the alter object privilege on the rule set if the rule set is in another user's schema. For example, to place a rule in the `human_resources` rule set in the `hr` schema, a user must be granted the `ALTER_ON_RULE_SET` privilege for the `hr.human_resources` rule set.

- The user must be granted the alter any system privilege for rule sets. For example, to place a rule in any rule set, a user must be granted the `ALTER_ANY_RULE_SET` system privilege.

In addition, the rule owner must have privileges on all objects referenced by the rule. These privileges are important when the rule does not have an evaluation context associated with it.

### Privileges for Evaluating a Rule Set

To evaluate a rule set, a user must meet at least one of the following conditions:

- The user must own the rule set.

- The user must be granted the execute object privilege on the rule set if it is in another user's schema. For example, to evaluate a rule set named `human_resources` in the `hr` schema, a user must be granted the `EXECUTE_ON_RULE_SET` privilege for the `hr.human_resources` rule set.

- The user must be granted the execute any system privilege for rule sets. For example, to evaluate any rule set, a user must be granted the `EXECUTE_ANY_RULE_SET` system privilege.

Granting `EXECUTE` object privilege on a rule set requires that the grantor have the `EXECUTE` privilege specified `WITH` `GRANT` `OPTION` on all rules currently in the rule set.

### Privileges for Using an Evaluation Context

To use an evaluation context in a rule or a rule set, the user who owns the rule or rule set must meet at least one of the following conditions for the evaluation context:

- The user must own the evaluation context.

- The user must be granted the `EXECUTE_ON_EVALUATION_CONTEXT` privilege on the evaluation context, if it is in another user's schema.

- The user must be granted the `EXECUTE_ANY_EVALUATION_CONTEXT` system privilege for evaluation contexts.